-- GB/T 26875.3—2011   《城市消防远程监控系统》 数据库表
--

-- 1 建筑消防设施系统状态
create table t_fire_facility_sys_status (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,

    data_time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    status bit(16)
);
create index idx_t_fire_facility_sys_time on t_fire_facility_sys_status(time);
create index idx_t_fire_facility_sys_status_sta on 
t_fire_facility_sys_status(time, src_addr, system_type, system_addr);

create table t_fire_facility_sys_status_state (
	id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int not null,
    time timestamp not null,

    data_time timestamp not null,
    src_addr char(12) not null,
    dest_addr char(12) not null,
    system_type smallint not null,
    system_addr smallint not null,

    status bit(16) not null
);
create UNIQUE index idx_t_fire_facility_sys_status_state_sta on 
t_fire_facility_sys_status_state(euid, system_type, system_addr);

create or replace function update_fire_facility_sys_status_state(euid_ text, 
    sequence_id_ integer, data_time_ timestamp, 
    dest_addr_ text, system_type_ smallint, system_addr_ smallint, status_ bit(16), clear_ boolean) 
returns void as $$
declare
pk_of_euid integer;
begin
    select id into pk_of_euid from t_node_info2 where euid = euid_;
    if found then
        if clear_ then
            update t_fire_facility_sys_status_state 
			set status = status&(~status_), clear = clear_
            where euid = pk_of_euid and system_type = system_type_
                and system_addr = system_addr_;

			insert into t_fire_facility_sys_status(euid, time, sequence_id, data_time, 
				src_addr, dest_addr, system_type, system_addr, status, clear)
			values (pk_of_euid, now(), sequence_id_, data_time_, euid_, dest_addr_, 
				system_type_, system_addr_, status_, clear_);
        else
            update t_fire_facility_sys_status_state 
            set status = status_,
                time = now(), 
                sequence_id = sequence_id_, 
                data_time = data_time_, 
                dest_addr = dest_addr_,
				clear = clear_
            where euid = pk_of_euid and system_type = system_type_
                and system_addr = system_addr_;

			insert into t_fire_facility_sys_status(euid, time, sequence_id, data_time, 
				src_addr, dest_addr, system_type, system_addr, status, clear)
				values (pk_of_euid, now(), sequence_id_, data_time_, euid_, dest_addr_, 
					system_type_, system_addr_, status_, clear_);
		end if;
        
    end if;
end;
$$ LANGUAGE plpgsql;

-- 2 上传建筑消防设施部件运行状态
create table t_fire_facility_part_status (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,

    data_time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,
    part_type smallint,
    part_area int,
    part_number int,
    part_desc varchar(31),      -- source is GB 18030--2005

    status bit(16)
);
create index idx_t_fire_facility_part_status_sta_pan on 
t_fire_facility_part_status(time, src_addr, system_type, system_addr, part_area, part_number);
create index idx_t_fire_facility_part_status_time on t_fire_facility_sys_status(time);

create table t_fire_facility_part_status_state (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,

    data_time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,
    part_type smallint,
    part_area int,
    part_number int,
    part_desc varchar(31),      -- source is GB 18030--2005

    status bit(16)
);
create UNIQUE index idx_t_fire_facility_part_status_state_sta_pan on 
t_fire_facility_part_status_state(euid, system_type, system_addr, part_area, part_number);

create or replace function update_t_fire_facility_part_status_state(euid_ text, 
    sequence_id_ integer, data_time_ timestamp, 
    dest_addr_ text, system_type_ smallint, system_addr_ smallint, 
    part_type_ smallint, part_area_ smallint, part_number_ smallint,
    status_ bit(16), part_desc_ text, clear_ boolean) 
returns void as $$
declare
pk_of_euid integer;
begin
    select id into pk_of_euid from t_node_info2 where euid = euid_;
    if found then
        if clear_ then
            update t_fire_facility_part_status_state set status = status&(~status_), clear=clear_
            where euid = pk_of_euid and system_type = system_type_
                and system_addr = system_addr_
                and part_area = part_area_
                and part_number = part_number_;

				insert into t_fire_facility_part_status(euid, time, sequence_id, data_time, 
					src_addr, dest_addr, system_type, system_addr, part_type, part_area, part_number,
					status, part_desc, clear)
				values (pk_of_euid, now(), sequence_id_, data_time_, euid_, dest_addr_, 
					system_type_, system_addr_, part_type_, part_area_, part_number_,
					status_, part_desc_, clear_);
		else
			update t_fire_facility_part_status_state 
			set status = status_,
                time = now(), 
                sequence_id = sequence_id_, 
                data_time = data_time_,
                dest_addr = dest_addr_,
                part_type = part_type_,
                part_desc = part_desc_,
				clear = clear_
            where euid = pk_of_euid and system_type = system_type_
                and system_addr = system_addr_
                and part_area = part_area_
                and part_number = part_number_;

                insert into t_fire_facility_part_status(euid, time, sequence_id, data_time, 
                    src_addr, dest_addr, system_type, system_addr, part_type, part_area, part_number,
                    status, part_desc, clear)
                    values (pk_of_euid, now(), sequence_id_, data_time_, euid_, dest_addr_, 
                        system_type_, system_addr_, part_type_, part_area_, part_number_,
                        status_, part_desc_, clear_);
        end if;
        
    end if;
end;
$$ LANGUAGE plpgsql;

-- 3 上传建筑消防设施部件模拟量值
create table t_fire_facility_part_analog (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,

    data_time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    part_type smallint,
    part_area int,
    part_number int,
    value_type smallint,
    value int,
);

-- 4 上传建筑消防设施操作信息
create table t_fire_facility_op_info (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,

    data_time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    operator_id smallint,
    status bit(8)
);

-- 5 上传建筑消防设施软件版本
create table t_fire_facility_version (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    major_version smallint,
    minor_version smallint
);

-- 6 上传建筑消防设施系统配置情况
create table t_fire_facility_sys_config (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    sys_description varchar(256) -- source is GB 18030--2005
);
-- 7 上传建筑消防设施部件配置情况
create table t_fire_facility_part_config (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    part_type smallint,
    part_area int,
    part_number int,
    part_desc varchar(31); -- source is GB 18030--2005
);

-- 8 上传建筑消防设施系统时间
create table t_fire_facility_sys_time (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    sys_time timestamp
);

-- 21 上传用户信息传输装置运行状态
create table t_fire_device_run_status (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    status bit(8),
    data_time timestamp
);
create index idx_t_fire_device_run_status_time on t_fire_device_run_status(time);
create index idx_t_fire_device_run_status_sta on 
t_fire_device_run_status(time, src_addr);

create table t_fire_device_run_status_state (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    status bit(8),
    data_time timestamp
);
create unique index idx_t_fire_device_run_status_state_sta on 
t_fire_device_run_status_state(euid, src_addr);

-- 24 上传用户信息传输装置操作信息
create table t_fire_device_op_info (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    status bit(8),
    operator_id smallint,
    data_time timestamp
);
create index idx_t_fire_device_op_info_time on t_fire_device_op_info(time);
create index idx_t_fire_device_op_info_sta on 
t_fire_device_op_info(time, src_addr);

create table t_fire_device_op_info_state (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    status bit(8),
    operator_id smallint,
    data_time timestamp
);
create unique index idx_t_fire_device_op_info_state_sta on 
t_fire_device_op_info_state(euid, src_addr);

-- 25 上传用户信息传输装置软件版本
create table t_fire_device_version (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    major_version smallint,
    minor_version smallint
);

-- 26 上传用户信息传输装置配置情况
create table t_fire_device_config (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12)
    system_type smallint,
    system_addr smallint,

    config_desc varchar(256)
);

-- 28 上传用户信息传输装置系统时间
create table t_fire_device_time (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    sys_time timestamp
);

/*
开始自定义，128
*/

-- 
-- 128 上传用户信息传输装置生产日期
create table t_fire_device_made_time (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    made_time timestamp
);

-- 129 上传用户报名信息
create table t_fire_user_signup_time (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    signup_time timestamp
);

-- 130 上传用户信息传输装置开机时间信息
create table t_fire_device_boot_time (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    operator_id smallint,
    bootup_time timestamp
);

-- 131 上传用户信息传输装置关机时间信息
create table t_fire_device_shutdown_time (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    operator_id smallint,
    shutdown_time timestamp
);

-- 132 上传建筑消防设施系统开机信息
create table t_fire_facility_boot_time (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    bootup_time timestamp
);
-- 133 上传建筑消防设施系统关机信息
create table t_fire_facility_shutdown_time (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    shutdown_time timestamp
);
-- 134 上传建筑消防设施系统状态恢复
create table t_fire_facility_restore(
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    status bit(16),
    data_time timestamp
);
-- 135 上传建筑消防设施部件运行状态恢复
create table t_fire_facility_part_restore (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),
    system_type smallint,
    system_addr smallint,

    part_type smallint,
    part_area int,
    part_number int,
    status bit(16),
    shutdown_time timestamp
);
-- 136 上传用户信息传输装置运行状态恢复
create table t_fire_device_restore (
    id bigserial primary key,
    euid bigint not null references t_node_info2(id),
    sequence_id int,
    time timestamp,
    src_addr char(12),
    dest_addr char(12),

    status bit(8),
    shutdown_time timestamp
);
